﻿using Dapper;
using System;
using BCEntity.OA.WorkflowTemplate.WorkflowTemplate;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;

namespace BCData.OA.WorkflowTemplate.WorkflowTemplate
{
	/// <summary>
	/// 工作流模板
	/// </summary>
	public class WorkflowTemplateData : IWorkflowTemplateData
	{
		private readonly IDatabaseContext databaseContext;
		private const string QUERY_EXISTS = @"SELECT 1 FROM oa_workflowtemplate";
		private const string QUERY = @"SELECT WorkflowTemplateId,Enabled,CreateAdminId,CreateAdminName,CreateTime,EditAdminId,EditAdminName,UpdateTime,TemplateCode,TemplateName,
											TemplateIcon,TemplateDescription,CategoryCode,CategoryName,IsBaseFlow,SpecificTemplateKey,IsMobleShow,AllowInitiate,IsAllPerson 
										FROM oa_workflowtemplate";
		public WorkflowTemplateData(IDatabaseContext databaseContext)
		{
			this.databaseContext = databaseContext;
		}
		#region 检查工作流模板是否存在
		public async Task<bool> Exists(long workflowTemplateId)
		{
			string sql = string.Format(@"{0} WHERE WorkflowTemplateId=?WorkflowTemplateId", QUERY_EXISTS);
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?WorkflowTemplateId", workflowTemplateId, DbType.Int64);
			return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
		}
		#endregion

		#region 添加工作流模板
		public async Task<WorkflowTemplateEntity> Add(WorkflowTemplateEntity entity)
		{
			string sql = SqlCommands.GetInsertSql("oa_workflowtemplate", new string[] { "Enabled", "CreateAdminId", "CreateAdminName", "TemplateCode", "TemplateName", 
				"CategoryCode", "CategoryName", "IsBaseFlow", "IsMobleShow","SpecificTemplateKey", "AllowInitiate", "IsAllPerson","TemplateIcon","TemplateDescription" });
			sql = string.Format("{0} {1} WHERE WorkflowTemplateId=LAST_INSERT_ID();", sql, QUERY);
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?Enabled", entity.Enabled, DbType.Boolean);
			parameters.Add("?CreateAdminId", entity.CreateAdminId, DbType.Int64, size: 20);
			parameters.Add("?CreateAdminName", entity.CreateAdminName?.Trim(), DbType.AnsiString, size: 20);
			parameters.Add("?TemplateCode", entity.TemplateCode?.Trim(), DbType.AnsiString, size: 50);
			parameters.Add("?TemplateName", entity.TemplateName?.Trim(), DbType.AnsiString, size: 100);
			parameters.Add("?TemplateIcon", entity.TemplateIcon?.Trim(), DbType.AnsiString, size: 1000);
			parameters.Add("?TemplateDescription", entity.TemplateDescription?.Trim(), DbType.AnsiString, size: 400);
			parameters.Add("?CategoryCode", entity.CategoryCode?.Trim(), DbType.AnsiString, size: 50);
			parameters.Add("?CategoryName", entity.CategoryName?.Trim(), DbType.AnsiString, size: 30);
			parameters.Add("?IsBaseFlow", entity.IsBaseFlow, DbType.Boolean);
			parameters.Add("?SpecificTemplateKey", entity.SpecificTemplateKey?.Trim(), DbType.AnsiString, size: 150);
			parameters.Add("?IsMobleShow", entity.IsMobleShow, DbType.Boolean);
			parameters.Add("?AllowInitiate", entity.AllowInitiate, DbType.Boolean);
			parameters.Add("?IsAllPerson", entity.IsAllPerson, DbType.Boolean);
			return await this.databaseContext.Master.QueryFirstOrDefaultAsync<WorkflowTemplateEntity>(sql, parameters, databaseContext.Transaction);

		}
		#endregion

		#region 修改工作流模板
		public async Task<WorkflowTemplateEntity> Update(WorkflowTemplateEntity entity)
		{
			string sql = SqlCommands.GetUpdateSql("oa_workflowtemplate", new string[] { "Enabled", "EditAdminId", "EditAdminName", "TemplateCode", "TemplateName", "TemplateIcon", 
				"TemplateDescription", "CategoryCode", "CategoryName", "IsBaseFlow", "IsMobleShow","SpecificTemplateKey", "AllowInitiate", "IsAllPerson" }, new string[] { "WorkflowTemplateId" });
			sql = string.Format("{0} {1} WHERE WorkflowTemplateId=?WorkflowTemplateId;", sql, QUERY);
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?WorkflowTemplateId", entity.WorkflowTemplateId, DbType.Int64, size: 20);
			parameters.Add("?Enabled", entity.Enabled, DbType.Boolean);
			parameters.Add("?EditAdminId", entity.EditAdminId, DbType.Int64, size: 20);
			parameters.Add("?EditAdminName", entity.EditAdminName?.Trim(), DbType.AnsiString, size: 20);
			parameters.Add("?TemplateCode", entity.TemplateCode?.Trim(), DbType.AnsiString, size: 50);
			parameters.Add("?TemplateName", entity.TemplateName?.Trim(), DbType.AnsiString, size: 100);
			parameters.Add("?TemplateIcon", entity.TemplateIcon?.Trim(), DbType.AnsiString, size: 1000);
			parameters.Add("?TemplateDescription", entity.TemplateDescription?.Trim(), DbType.AnsiString, size: 400);
			parameters.Add("?CategoryCode", entity.CategoryCode?.Trim(), DbType.AnsiString, size: 50);
			parameters.Add("?CategoryName", entity.CategoryName?.Trim(), DbType.AnsiString, size: 30);
			parameters.Add("?IsBaseFlow", entity.IsBaseFlow, DbType.Boolean);
			parameters.Add("?SpecificTemplateKey", entity.SpecificTemplateKey?.Trim(), DbType.AnsiString, size: 150);
			parameters.Add("?IsMobleShow", entity.IsMobleShow, DbType.Boolean);
			parameters.Add("?AllowInitiate", entity.AllowInitiate, DbType.Boolean);
			parameters.Add("?IsAllPerson", entity.IsAllPerson, DbType.Boolean);
			return await this.databaseContext.Master.QueryFirstOrDefaultAsync<WorkflowTemplateEntity>(sql, parameters, databaseContext.Transaction);
		}
		#endregion

		#region 删除工作流模板
		public async Task<bool> Delete(long workflowTemplateId)
		{
			string sql = @"DELETE FROM oa_workflowtemplate WHERE WorkflowTemplateId=?WorkflowTemplateId";
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?WorkflowTemplateId", workflowTemplateId, DbType.Int64);
			return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
		}
		#endregion

		#region 获取工作流模板
		public async Task<WorkflowTemplateEntity> GetEntity(long workflowTemplateId)
		{
			string sql = string.Format(@"{0} WHERE WorkflowTemplateId=?WorkflowTemplateId", QUERY);
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?WorkflowTemplateId", workflowTemplateId, DbType.Int64);
			return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<WorkflowTemplateEntity>(sql, parameters);
		}
		public async Task<WorkflowTemplateEntity> GetEntity(string workflowTemplateCode)
		{
			string sql = string.Format(@"{0} WHERE WorkflowTemplateId=?WorkflowTemplateId", QUERY);
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?TemplateCode", workflowTemplateCode?.Trim(), DbType.AnsiString, size: 50);
			return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<WorkflowTemplateEntity>(sql, parameters);
		}
		public async Task<WorkflowTemplateEntity> GetBySpecificKey(string specificTemplateKey)
        {
			string sql = string.Format(@"{0} WHERE SpecificTemplateKey=?SpecificTemplateKey", QUERY);
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?SpecificTemplateKey", specificTemplateKey?.Trim(), DbType.AnsiString, size: 150);
			return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<WorkflowTemplateEntity>(sql, parameters);
		}
		#endregion

		#region 获取工作流模板列表
		public async Task<IEnumerable<WorkflowTemplateEntity>> GetList(string categoryCode)
		{
			string sql = string.Format(@"{0} WHERE CategoryCode=?CategoryCode", QUERY);
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?CategoryCode", categoryCode.Trim(), DbType.AnsiString, size: 50);
			return await this.databaseContext.Slave.QueryAsync<WorkflowTemplateEntity>(sql, parameters);
		}
		#endregion

		#region 获取工作流模板分页
		public async Task<(IEnumerable<WorkflowTemplateEntity>, int)> Query(string categoryCode,string templateName,bool? isBaseFlow,bool? isMobleShow, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
		{
			List<string> wheres = new List<string>();
			DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(categoryCode))
            {
				wheres.Add("CategoryCode=?CategoryCode");
				parameters.Add("?CategoryCode", categoryCode.Trim(), DbType.AnsiString, size: 50);
			}
			if (!string.IsNullOrWhiteSpace(templateName))
			{
				wheres.Add("TemplateName LIKE CONCAT('%',CONCAT(?TemplateName,'%'))");
				parameters.Add("?TemplateName", templateName.Trim(), DbType.AnsiString);
			}
			if (isBaseFlow.HasValue)
			{
				wheres.Add("IsBaseFlow=?IsBaseFlow");
				parameters.Add("?IsBaseFlow", isBaseFlow.Value, DbType.Boolean);
			}
			if (isMobleShow.HasValue)
			{
				wheres.Add("IsMobleShow=?IsMobleShow");
				parameters.Add("?IsMobleShow", isMobleShow.Value, DbType.Boolean);
			}
			if (start.HasValue && !end.HasValue)
			{
				wheres.Add("CreateTime>=?Start");
				parameters.Add("?Start", start.Value, DbType.DateTime);
			}
			else if (!start.HasValue && end.HasValue)
			{
				wheres.Add("CreateTime<=?End");
				parameters.Add("?End", end.Value, DbType.DateTime);
			}
			else if (start.HasValue && end.HasValue)
			{
				wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
				parameters.Add("?Start", start.Value, DbType.DateTime);
				parameters.Add("?End", end.Value, DbType.DateTime);
			}
			string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM oa_workflowtemplate {1}",
				  QUERY,
				  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
				  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
				  pageSize,
				  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
				  ascending.HasValue && ascending.Value ? "ASC" : "DESC");
			return await this.databaseContext.Slave.QueryMultipleAsync<WorkflowTemplateEntity, int>(sql, parameters);
		}
		#endregion

		#region 获取允许我发起的工作流列表
		public async Task<IEnumerable<WorkflowTemplateEntity>> GetAllowInitiateList(int companyId, int? departmentId, long adminId, int positionId)
		{
			string sql = @"SELECT A.WorkflowTemplateId,A.Enabled,A.CreateAdminId,A.CreateAdminName,A.CreateTime,A.EditAdminId,A.EditAdminName,A.UpdateTime,A.TemplateCode,
								A.TemplateName,A.CategoryCode,A.CategoryName,A.IsBaseFlow,A.SpecificTemplateKey,A.IsMobleShow,A.AllowInitiate,A.IsAllPerson 
							FROM oa_workflowtemplate A
							WHERE A.AllowInitiate=1 AND A.IsAllPerson=1
							UNION
							SELECT A.WorkflowTemplateId,A.Enabled,A.CreateAdminId,A.CreateAdminName,A.CreateTime,A.EditAdminId,A.EditAdminName,A.UpdateTime,A.TemplateCode,
								A.TemplateName,A.CategoryCode,A.CategoryName,A.IsBaseFlow,A.SpecificTemplateKey,A.IsMobleShow,A.AllowInitiate,A.IsAllPerson 
							FROM oa_workflowtemplate A
							INNER JOIN OA_WorkflowTemplatePermission B ON B.PermissionType='CompanyId'
							WHERE A.AllowInitiate=1 AND A.IsAllPerson=0 AND B.PermissionReferenceId=?CompanyId
							UNION
							SELECT A.WorkflowTemplateId,A.Enabled,A.CreateAdminId,A.CreateAdminName,A.CreateTime,A.EditAdminId,A.EditAdminName,A.UpdateTime,A.TemplateCode,
								A.TemplateName,A.CategoryCode,A.CategoryName,A.IsBaseFlow,A.SpecificTemplateKey,A.IsMobleShow,A.AllowInitiate,A.IsAllPerson 
							FROM oa_workflowtemplate A
							INNER JOIN OA_WorkflowTemplatePermission B ON B.PermissionType='Human'
							WHERE A.AllowInitiate=1 AND A.IsAllPerson=0 AND B.PermissionReferenceId=?AdminId
							UNION
							SELECT A.WorkflowTemplateId,A.Enabled,A.CreateAdminId,A.CreateAdminName,A.CreateTime,A.EditAdminId,A.EditAdminName,A.UpdateTime,A.TemplateCode,
								A.TemplateName,A.CategoryCode,A.CategoryName,A.IsBaseFlow,A.SpecificTemplateKey,A.IsMobleShow,A.AllowInitiate,A.IsAllPerson 
							FROM oa_workflowtemplate A
							INNER JOIN OA_WorkflowTemplatePermission B ON B.PermissionType='Position'
							WHERE A.AllowInitiate=1 AND A.IsAllPerson=0 AND B.PermissionReferenceId=?PositionId";
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?CompanyId", companyId, DbType.Int32);
			parameters.Add("?AdminId", adminId, DbType.Int32);
			parameters.Add("?PositionId", positionId, DbType.Int64);

			if (departmentId.HasValue)
			{
				sql += @"
					UNION
					SELECT A.WorkflowTemplateId,A.Enabled,A.CreateAdminId,A.CreateAdminName,A.CreateTime,A.EditAdminId,A.EditAdminName,A.UpdateTime,A.TemplateCode,
						A.TemplateName,A.CategoryCode,A.CategoryName,A.IsBaseFlow,A.SpecificTemplateKey,A.IsMobleShow,A.AllowInitiate,A.IsAllPerson 
					FROM oa_workflowtemplate A
					INNER JOIN OA_WorkflowTemplatePermission B ON B.PermissionType='DepartmentId'
					WHERE A.AllowInitiate=1 AND A.IsAllPerson=0 AND B.PermissionReferenceId=?DepartmentId";
				parameters.Add("?DepartmentId", departmentId.Value, DbType.Int32);

			}

			return await this.databaseContext.Slave.QueryAsync<WorkflowTemplateEntity>(sql, parameters);
		}
		#endregion
	}
}



